package com.zhang.publisher.mapper;


import com.zhang.publisher.bean.VisitorStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @title: 流量Mapper
 * @author: zhang
 * @date: 2022/3/13 14:55
 */
public interface VisitorStatsMapper {


    //新老访客流量统计
    @Select(" select is_new,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct,sum(sv_ct) sv_ct," +
            " sum(uj_ct) uj_ct,sum(dur_sum) dur_sum " +
            " from visitor_stats_2022 where toYYYYMMDD(stt)=#{date} " +
            " group by is_new " )
    List<VisitorStats> selectVisitorStats(@Param("date") Integer date);

    /**
     *  ClickHouse的函数
     *      -if(条件表达式，满足返回的值，不满足返回的值)
     *      -toHour
     *         将日期转换为小时 （0~23）
     */
    //新老访客分时统计
    @Select(" select toHour(stt) hr,sum(visitor_stats_2022.uv_ct) uv_ct,sum(pv_ct) pv_ct, " +
            " sum(if(is_new='1',visitor_stats_2022.uv_ct,0)) new_uv " +
            " from visitor_stats_2022 " +
            " where toYYYYMMDD(stt)=#{date}" +
            " group by toHour(stt)" )
    List<VisitorStats> selectVisitorByHr(@Param("date") Integer date);
}
